/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package testdockerdb.carcontroller.services;

import java.net.Socket;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import testdockerdb.carcontroller.models.CarPos;
import testdockerdb.carcontroller.models.CarPosData;
import testdockerdb.carcontroller.models.CarPosInfo;
import testdockerdb.carcontroller.models.CarPosResult;
import testdockerdb.carcontroller.models.Coordinate;

/**
 *
 * @author wuhao
 */
public class DbOperation {

        String DB_URL = "jdbc:mysql://mysqlctrl:3306/rentcar", DB_USER = "root", DB_PASSWD = "root";
        // String DB_URL = "jdbc:mysql://localhost:3306/rentcar", DB_USER = "root", DB_PASSWD = "root";
        private Socket client = null;
        java.sql.Connection connection = null;
        Statement stmt = null;

        public DbOperation() {
                DbConnInit();
        }

        void DbConnInit() {
                try {
                        connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWD);
                        stmt = connection.createStatement();
                } catch (SQLException ex) {
                        Logger.getLogger(DbOperation.class.getName()).log(Level.SEVERE, null, ex);
                }
        }

        public void SaveToDb(CarPosInfo cpi) {
                if (cpi == null) {
                        System.out.println("CarPosInfo is null!");
                        return;
                }
                CarPosData cpd = cpi.getData();

                System.out.println("Connecting database...");

                try {
                        System.out.println("Database connected!");
                        String sql = String.format("insert into posdata2(systemno,time,latitude,longitude,speed,direction,islocation) values"
                                + "('%s','%s',%f,%f,%f,%f,%d) ON DUPLICATE KEY UPDATE time='%s',latitude='%f',longitude='%f',speed='%f',direction='%f',islocation='%d'",
                                cpi.getSystemno(), cpd.getTime(), cpd.getLat(), cpd.getLon(), cpd.getSpeed(), cpd.getDirection(), cpd.getIslocation(),
                                cpd.getTime(), cpd.getLat(), cpd.getLon(), cpd.getSpeed(), cpd.getDirection(), cpd.getIslocation());

                        System.out.println(sql);
                        stmt.executeUpdate(sql);
                } catch (SQLException e) {
                        throw new IllegalStateException("Cannot connect the database!", e);
                }
        }

        public CarPosResult GetCurPos(String vin) {

                Date time = new Date();
                double lat = 0, lon = 0, speed = 0;
                boolean succeed = false;
                //数据时间和当前时间之差大于10秒，则认为数据是无效定位
                int MaxStep = 10000;
                String query = "select time, latitude, longitude,speed from posdata2 where systemno='" + vin + "'";
                try {
                        //stmt = connection.createStatement();
                        ResultSet rs = stmt.executeQuery(query);
                        while (rs.next()) {
                                succeed = true;
                                time = rs.getDate("time");
                                lat = rs.getDouble("latitude");
                                lon = rs.getDouble("longitude");
                                speed = rs.getDouble("speed");
                                break;
                        }
                } catch (SQLException e) {
                        Logger.getLogger(DbOperation.class.getName()).log(Level.SEVERE, null, e);
                } finally {
                        // if (stmt != null) { stmt.close(); }
                }
                Coordinate c = new Coordinate();
                c.setLon(lon);
                c.setLat(lat);

                CarPos cp = new CarPos();
                cp.setLocation(c);
                cp.setRate(speed);
                long lNow = (new Date()).getTime();
                if (lNow - time.getTime() > MaxStep) {
                        //时间过长，无效定位
                        cp.setStat("100");
                } else {
                        cp.setStat("000");
                }

                CarPosResult cpr = new CarPosResult();
                cpr.setResults(cp);
                if (succeed == true) {
                        cpr.setRet("0");
                } else {
                        cpr.setRet("-1");
                }
                return cpr;
        }
}
